import sys
import os

# 添加项目根目录到系统路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from database import Database

class StudentQuery:
    def __init__(self):
        self.db = Database()
    
    def query_all_students(self):
        """
        查询所有学生信息
        
        返回:
            list: 学生信息列表
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, student_id, name, gender, class_name, contact, dormitory_id FROM student ORDER BY student_id"
            )
            students = self.db.cursor.fetchall()
            return students
        except Exception as e:
            print(f"错误：查询学生时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_student_by_id(self, student_id):
        """
        根据学号查询学生信息
        
        参数:
            student_id (str): 学号
            
        返回:
            tuple: 学生信息
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, student_id, name, gender, class_name, contact, dormitory_id FROM student WHERE student_id = ?",
                (student_id,)
            )
            student = self.db.cursor.fetchone()
            return student
        except Exception as e:
            print(f"错误：查询学生时发生错误 - {str(e)}")
            return None
        finally:
            self.db.close()
    
    def query_students_by_name(self, name):
        """
        根据姓名查询学生信息（支持模糊查询）
        
        参数:
            name (str): 学生姓名
            
        返回:
            list: 学生信息列表
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, student_id, name, gender, class_name, contact, dormitory_id FROM student WHERE name LIKE ?",
                (f"%{name}%",)
            )
            students = self.db.cursor.fetchall()
            return students
        except Exception as e:
            print(f"错误：查询学生时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_students_by_class(self, class_name):
        """
        根据班级查询学生信息
        
        参数:
            class_name (str): 班级名称
            
        返回:
            list: 学生信息列表
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, student_id, name, gender, class_name, contact, dormitory_id FROM student WHERE class_name = ?",
                (class_name,)
            )
            students = self.db.cursor.fetchall()
            return students
        except Exception as e:
            print(f"错误：查询学生时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_students_by_dormitory(self, dormitory_id):
        """
        根据宿舍ID查询学生信息
        
        参数:
            dormitory_id (int): 宿舍ID
            
        返回:
            list: 学生信息列表
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, student_id, name, gender, class_name, contact, dormitory_id FROM student WHERE dormitory_id = ?",
                (dormitory_id,)
            )
            students = self.db.cursor.fetchall()
            return students
        except Exception as e:
            print(f"错误：查询学生时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()

# 如果直接运行此文件，提供命令行界面
if __name__ == "__main__":
    query = StudentQuery()
    
    print("===== 学生信息查询 =====")
    print("1. 查询所有学生")
    print("2. 根据学号查询学生")
    print("3. 根据姓名查询学生")
    print("4. 根据班级查询学生")
    print("5. 根据宿舍查询学生")
    
    choice = input("请选择查询方式 (1-5): ")
    
    if choice == '1':
        students = query.query_all_students()
        if students:
            print("\n学生ID\t学号\t姓名\t性别\t班级\t联系方式\t宿舍ID")
            for student in students:
                dormitory_id = student[6] if student[6] else "未分配"
                print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}\t{dormitory_id}")
        else:
            print("没有找到任何学生信息")
    
    elif choice == '2':
        student_id = input("请输入学号: ")
        student = query.query_student_by_id(student_id)
        
        if student:
            print("\n学生ID\t学号\t姓名\t性别\t班级\t联系方式\t宿舍ID")
            dormitory_id = student[6] if student[6] else "未分配"
            print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}\t{dormitory_id}")
        else:
            print(f"没有找到学号为 {student_id} 的学生信息")
    
    elif choice == '3':
        name = input("请输入姓名(支持模糊查询): ")
        students = query.query_students_by_name(name)
        
        if students:
            print("\n学生ID\t学号\t姓名\t性别\t班级\t联系方式\t宿舍ID")
            for student in students:
                dormitory_id = student[6] if student[6] else "未分配"
                print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}\t{dormitory_id}")
        else:
            print(f"没有找到姓名包含 '{name}' 的学生信息")
    
    elif choice == '4':
        class_name = input("请输入班级: ")
        students = query.query_students_by_class(class_name)
        
        if students:
            print("\n学生ID\t学号\t姓名\t性别\t班级\t联系方式\t宿舍ID")
            for student in students:
                dormitory_id = student[6] if student[6] else "未分配"
                print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}\t{dormitory_id}")
        else:
            print(f"没有找到班级为 {class_name} 的学生信息")
    
    elif choice == '5':
        try:
            dormitory_id = int(input("请输入宿舍ID: "))
            students = query.query_students_by_dormitory(dormitory_id)
            
            if students:
                print("\n学生ID\t学号\t姓名\t性别\t班级\t联系方式\t宿舍ID")
                for student in students:
                    print(f"{student[0]}\t{student[1]}\t{student[2]}\t{student[3]}\t{student[4]}\t{student[5] or '无'}\t{student[6]}")
            else:
                print(f"没有找到宿舍ID为 {dormitory_id} 的学生信息")
        except ValueError:
            print("错误：宿舍ID必须是数字")
    
    else:
        print("无效的选择")